<?php

class Frontendmodel extends CI_Model {

    function __construct() {
        parent::__construct();
    }

    //news funcs
    function get_latest_news($start, $count) {
        $db = $this->load->database('default', TRUE);
        $sql = "SELECT news.news_id,news.title,news.short_desc,news.img_thumb,news.img_thumb2,news.img_thumb3,news.url,news.parent_cate_url,news_cate.url as news_cate_url 
                FROM news INNER JOIN news_cate on news_cate.cate_id = news.cate_id 
                WHERE news.status='1' and DATEDIFF(CURDATE(),news.online_date) >= 0 
                ORDER BY news.online_date desc
                LIMIT ?,?;";
        $query = $db->query($sql, array($start, $count));
        return array($query->result());
    }

    function get_feature_news($start, $count) {
        $db = $this->load->database('default', TRUE);
        $sql = "SELECT news.news_id,news.title,news.short_desc,news.img,news.img_thumb,
            news.img_thumb2,news.img_thumb3,news.url,news.parent_cate_url,news.parent_cate_url,
            news_cate.cate_name,news_cate.url as news_cate_url, news.cate_id 
                FROM  news INNER JOIN news_cate on news_cate.cate_id = news.cate_id
                WHERE news.is_hot='1' and news.status='1' and DATEDIFF(CURDATE(),online_date)<=" . FEATUREDNEWS_DAYS
                . " ORDER BY news.online_date desc 
                LIMIT ?,?;";
        $query = $db->query($sql, array($start, $count));
        return array($query->result());
    }

    function get_most_view_news($cate_id, $start, $count) {
        $db = $this->load->database('default', TRUE);
        $sql = "SELECT news.news_id,news.title,news.short_desc,news.url,news.img_thumb,
            news.img_thumb2,news.img_thumb3,news.parent_cate_url,news.display_date,
            news_cate.cate_name,news_cate.url as news_cate_url 
                FROM  news INNER JOIN news_cate on news_cate.cate_id = news.cate_id
                WHERE news.status='1' and DATEDIFF(CURDATE(),online_date)<=" . MOSTREADNEWS_DAYS . " and news.cate_id=?"
                . " ORDER BY views_count desc 
                LIMIT ?,?;";
        $query = $db->query($sql, array($cate_id, $start, $count));
        return array($query->result());
    }

    function get_cities_list() {
        $xml = $this->_get_xml("./application/data/weather/city.xml");
        $array = array();
        foreach ($xml->children() as $city) {
            $arr = explode("|", $city);
            $array[] = $arr;
        }
        return $array;
    }

    function get_list_news_info($news_cate_id) {
        $db = $this->load->database('default', TRUE);
        $sql = "SELECT news.news_id,news_cate.cate_id,news_cate.parent_id 
                FROM  news INNER JOIN news_cate on news_cate.cate_id = news.cate_id
                WHERE news.status='1' and news.cate_id = ?
                ORDER BY news.news_id DESC
                LIMIT ?,?;";
        $query = $db->query($sql, array($news_cate_id, 0, MAX_NEWS_LIST_CACHED));
        return $query->result();
    }

    function get_desc($id) {
        $db = $this->load->database('default', true);
        $sql = "SELECT news.news_id,news.title,news.short_desc,news.img_thumb,news.img_thumb2,news.img_thumb3,news.url,news.parent_cate_url,news.display_date,news_cate.cate_name,news_cate.url as news_cate_url
                FROM news INNER JOIN news_cate on news_cate.cate_id = news.cate_id
                WHERE news.status='1' and news.news_id=?;";
        $query = $db->query($sql, $id);
        return $query->result();
    }

    function _get_news_nopaging($field, $where, $order, $limit_from, $limit_to) {
        $db = $this->load->database('default', TRUE);
        $sql = "SELECT " . $field . " from news 
						inner join news_cate on news_cate.cate_id = news.cate_id		
			 		  where " . $where . $order . " LIMIT {$limit_from},{$limit_to};";
        $query = $db->query($sql);

        return array($query->result());
    }

    function get_news_paging($news_cate_id, $start, $count) {
        $db = $this->load->database('default', TRUE);
        $sql = "SELECT count(news.news_id) c 
            FROM news INNER JOIN news_cate on news_cate.cate_id = news.cate_id		
            WHERE news.cate_id in (?) and news.status='1' ORDER BY news_id DESC;";
        $query = $db->query($sql, array($news_cate_id));
        $aRows = $query->result();
        $iRow_Count = $aRows[0]->c;
        $sql = "SELECT news.news_id,news.title,news.short_desc,news.file,news.img_thumb,news.img_thumb2,news.img_thumb3,news.url,news.parent_cate_url,news.create_date,news.display_date,news_cate.cate_name,news_cate.url as news_cate_url
                FROM news INNER JOIN news_cate on news_cate.cate_id = news.cate_id		
                WHERE news.cate_id in (?) and news.status='1' 
                order by online_date desc
                LIMIT ?,?;";
        $query = $db->query($sql, array($news_cate_id, $start, $count));
        $result = $query->result();
        return array($iRow_Count, $result);
    }

    function get_news_cate_nopaging($parentid, $start, $count) {
        $db = $this->load->database('default', TRUE);
        $sql = "SELECT cate_id,cate_name,url 
                FROM news_cate 
                WHERE parent_id=? and news_cate.status='1' order by news_cate.cate_id desc LIMIT ?,?;";

        $query = $db->query($sql, array($parentid, $start, $count));
        return array($query->result());
    }

    function get_news_detail($id) {
        $db = $this->load->database('default', TRUE);
        $sql = "SELECT title,short_desc,full_desc,img,img_thumb,parent_cate_url,news.cate_id, news_cate.parent_id, 
                news.source, news.display_date, news.views_count
                FROM news INNER JOIN news_cate on news_cate.cate_id = news.cate_id
                WHERE news_id=? and news.status='1';";

        $query = $db->query($sql, $id);
        return $query->result();
    }

    function increse_view_count($id, $view_count) {
        $db = $this->load->database('default', TRUE);
        $db->where('news_id', $id);
        $result = $db->update('news', array('views_count' => $view_count));
        return $result;
    }

    function _get_news_cate_list() {
        $db = $this->load->database('default', TRUE);
        $sql = "SELECT * FROM news_cate order by cate_id desc";
        $query = $db->query($sql);
        return $query->result();
    }

    function _get_news_cate_by_parent_id($parent_id) {
        $db = $this->load->database('default', TRUE);
        $sql = "SELECT cate_id FROM news_cate where parent_id=" . $parent_id;
        $query = $db->query($sql);
        return $query->result();
    }

    //FAQ funcs
    function _get_faq_nopaging($limit_start, $limit_count) {
        $db = $this->load->database('default', TRUE);
        $sql = "SELECT question, answer FROM faq where status='1' order by faq_id desc LIMIT ?,?";
        $query = $db->query($sql, array($limit_start, $limit_count));
        return array($query->result());
    }

    function _get_faq_paging($limit_from, $limit_to) {
        $db = $this->load->database('default', TRUE);
        $sql = "SELECT count(faq.faq_id) c from faq 
			 		  where faq.status='1' order by faq_id desc;";
        $query = $db->query($sql);
        $aRows = $query->result();
        $iRow_Count = $aRows[0]->c;
        $sql = "SELECT faq.*, faq_cate.cate_name from faq left join faq_cate on faq.faq_cate_id = faq_cate.cate_id
			 		  where faq.status='1' order by faq_id desc LIMIT ?,?;";
        $query = $db->query($sql, array($limit_from, $limit_to));
        $result = $query->result();
        return array($iRow_Count, $result);
    }

    //details funcs	

    function _get_details_nojoin($field, $table_name, $where) {
        $db = $this->load->database('default', TRUE);
        $sql = "SELECT " . $field . " from " . $table_name . " where " . $where;
        $query = $db->query($sql);
        return $query->result();
    }

    function _update_config($id) {
        $db = $this->load->database('default', TRUE);
        $sql = "update config set page_views=page_views+1 where config_id=" . $id;
        $query = $db->query($sql);
        $sql = "select page_views from config where config_id=" . $id;
        $query = $db->query($sql);
        $aRows = $query->result();
        $page_views = $aRows[0]->page_views;
        return $page_views;
    }

    //check login
    function _check_views($session_key) {
        if (empty($_SESSION[$session_key])) {
            return false;
        } else {
            return true;
        }
    }

    //get session
    function _get_session($session_key) {
        if (isset($_SESSION[$session_key]))
            return $_SESSION[$session_key];
    }

    //get session
    function _set_session($session_key, $value) {
        $_SESSION[$session_key] = $value;
    }

    //remove session
    function _remove_session($session_key) {
        unset($_SESSION[$session_key]);
    }

    //news search
    function _search_news_list($cate_id, $keywords, $limit_from, $limit_to) {
        $db = $this->load->database('default', TRUE);
        $sql = "select count(news.news_id) c FROM news
						inner join news_cate on news_cate.cate_id = news.cate_id
						where 1=1";
        if ($keywords != "")
            $sql.=" and news.title like N'%" . $keywords . "%' ";
        if ($cate_id != "")
            $sql.=" and news.cate_id=" . $cate_id;
        $sql.=" order by news_id DESC;";
        $query = $db->query($sql);
        $aRows = $query->result();
        $iRow_Count = $aRows[0]->c;

        $sql = "select news.*, news_cate.cate_name FROM news
						inner join news_cate on news_cate.cate_id = news.cate_id
						where 1=1";
        if ($keywords != "")
            $sql.=" and news.title like N'%" . $keywords . "%' ";
        if ($cate_id != "")
            $sql.=" and news.cate_id=" . $cate_id;
        $sql.=" order by news_id DESC LIMIT {$limit_from},{$limit_to};";
        $query = $db->query($sql);
        $result = $query->result();
        return array($iRow_Count, $result);
    }

    function _insert_contact_us($tablename, $data) {
        //$sql = $this->db->query("SET NAMES 'utf8'");
        $db = $this->load->database('default', TRUE);
        $db->insert($tablename, $data);
    }

    function _get_list_video($from_limit, $numberof, $titleSearch) {
        $db = $this->load->database('default', TRUE);
        $count = $db->count_all('video');

        $db->order_by("video_id", "desc");
        $db->like('title', $titleSearch);
        $query = $db->get('video', $numberof, $from_limit);
        $result = $query->result();
        return array($count, $result);
    }

    function _get_all_cate_customs() {
        $db = $this->load->database('default', TRUE);
        $db->order_by("cate_index", "asc");
        $query = $db->get('customs_procedures_cate');
        $result = $query->result();
        return $result;
    }

    function _get_xml($xmlpath) {
        $xml = simplexml_load_file($xmlpath);
        return $xml;
    }

    function _get_list_customs($customs_name, $customs_cate, $page_size, $page_index){
        $db = $this->load->database('default', TRUE);

        //get row number
        if($customs_name != ""){
            $db->like('customs_name', $customs_name);
        }
        
        if($customs_cate != 0){
            $arrCate = explode("|", $customs_cate);
            for($i=0; $i<sizeof($arrCate)-1; $i++){
                $db->or_where("customs_cate", $arrCate[$i]);
            }
            $query = $db->get("customs_procedures");
        }
        else{
            $query = $db->get('customs_procedures');
        }
        $count = $query->num_rows();

        //get result with paging
        if($customs_name != ""){
            $db->like('customs_name', $customs_name);
        }

        if($customs_cate != 0){
            $arrCate = explode("|", $customs_cate);
            for($i=0; $i<sizeof($arrCate)-1; $i++){
                $db->or_where("customs_cate", $arrCate[$i]);
            }
            $query = $db->get("customs_procedures", $page_size, $page_index);
        }
        else{
            $query = $db->get('customs_procedures', $page_size, $page_index);
        }

        $result = $query->result();
        return array($count, $result);
    }

    function _get_customs_by_id($customsId){
        $db = $this->load->database('default', TRUE);
        $query = $db->get_where('customs_procedures', array("customs_id" => $customsId));
        $result = $query->result();
        return $result;
    }

    // hàm nay để test nested array
    function do_dump(&$var, $var_name = NULL, $indent = NULL, $reference = NULL) {
        $do_dump_indent = "<span style='color:#eeeeee;'>|</span> &nbsp;&nbsp; ";
        $reference = $reference . $var_name;
        $keyvar = 'the_do_dump_recursion_protection_scheme';
        $keyname = 'referenced_object_name';

        if (is_array($var) && isset($var[$keyvar])) {
            $real_var = &$var[$keyvar];
            $real_name = &$var[$keyname];
            $type = ucfirst(gettype($real_var));
            echo "$indent$var_name <span style='color:#a2a2a2'>$type</span> = <span style='color:#e87800;'>&amp;$real_name</span><br>";
        } else {
            $var = array($keyvar => $var, $keyname => $reference);
            $avar = &$var[$keyvar];

            $type = ucfirst(gettype($avar));
            if ($type == "String")
                $type_color = "<span style='color:green'>";
            elseif ($type == "Integer")
                $type_color = "<span style='color:red'>";
            elseif ($type == "Double") {
                $type_color = "<span style='color:#0099c5'>";
                $type = "Float";
            } elseif ($type == "Boolean")
                $type_color = "<span style='color:#92008d'>";
            elseif ($type == "NULL")
                $type_color = "<span style='color:black'>";

            if (is_array($avar)) {
                $count = count($avar);
                echo "$indent" . ($var_name ? "$var_name => " : "") . "<span style='color:#a2a2a2'>$type ($count)</span><br>$indent(<br>";
                $keys = array_keys($avar);
                foreach ($keys as $name) {
                    $value = &$avar[$name];
                    $this->do_dump($value, "['$name']", $indent . $do_dump_indent, $reference);
                }
                echo "$indent)<br>";
            } elseif (is_object($avar)) {
                echo "$indent$var_name <span style='color:#a2a2a2'>$type</span><br>$indent(<br>";
                foreach ($avar as $name => $value)
                    $this->do_dump($value, "$name", $indent . $do_dump_indent, $reference);
                echo "$indent)<br>";
            } elseif (is_int($avar))
                echo "$indent$var_name = <span style='color:#a2a2a2'>$type(" . strlen($avar) . ")</span> $type_color$avar</span><br>";
            elseif (is_string($avar))
                echo "$indent$var_name = <span style='color:#a2a2a2'>$type(" . strlen($avar) . ")</span> $type_color\"$avar\"</span><br>";
            elseif (is_float($avar))
                echo "$indent$var_name = <span style='color:#a2a2a2'>$type(" . strlen($avar) . ")</span> $type_color$avar</span><br>";
            elseif (is_bool($avar))
                echo "$indent$var_name = <span style='color:#a2a2a2'>$type(" . strlen($avar) . ")</span> $type_color" . ($avar == 1 ? "TRUE" : "FALSE") . "</span><br>";
            elseif (is_null($avar))
                echo "$indent$var_name = <span style='color:#a2a2a2'>$type(" . strlen($avar) . ")</span> {$type_color}NULL</span><br>";
            else
                echo "$indent$var_name = <span style='color:#a2a2a2'>$type(" . strlen($avar) . ")</span> $avar<br>";

            $var = $var[$keyvar];
        }
    }
    
    function getContentIndex($idCol, $tableName){
        $db = $this->load->database('default', TRUE);
        $db->order_by($idCol, "RANDOM");
        $query = $db->get($tableName, 10);
        $result = $query->result();
        return array($result);
    }

}

?>